#!/usr/bin/perl
# 
# Read the geographical information of campus map
# Insert data to sqlite database
# Author Hui Liu
use strict;
use warnings;
use DBI;

my $mapFile = $ARGV[0]? $ARGV[0]:"Towson_Buildings_Etc.txt";
my $dbh = DBI->connect("dbi:SQLite:../assets/tourMap.db","","",{RaiseError => 1, sqlite_unicode => 1, AutoCommit => 1});

open my $fh, $mapFile or die "Can't open file $mapFile";

my $bname;
my $lat;
my $lng;
my $ltype = 'B';

# Insert building and Parking lots
#$dbh->do("INSERT INTO locations VALUES (NULL, $bname, $lat, $lng, $ltype, NULL, NULL, NULL, NULL)");


# clear table

$dbh->do("DROP TABLE IF EXISTS location_points;");
$dbh->do("CREATE TABLE location_points (_id INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL , latitude FLOAT, longitude FLOAT, locationID INTEGER, FOREIGN KEY(locationID) REFERENCES locations(_id));");

my $locIndex;

while (my $line = <$fh>) {
    chomp $line;

    
    if ($line =~ /^<polygon name=\"(.*)\"/) {
       #$line =~ /^<polygon name=\"(.*)\"/;
        $bname = $1;
        # find building id
        my $route = $dbh->prepare('SELECT _id FROM locations WHERE name = ?')
                or die "Couldn't prepare statement: " . $dbh->errstr;
        $route->execute($bname) or die "Couldn't execute statement: " . $route->errstr;
        my @row = $route->fetchrow_array();
        $locIndex = $row[0];
#        print $bname, $locIndex, "\n";
    }elsif ($line =~ /^<point lat=\"(.*)\" lng=\"(.*)\"/) {
        #$line =~ /^<point lat=\"(.*)\" lng=\"(.*)\"/;
#        print "INSERT INTO location_points VALUES (NULL, $1, $2, $locIndex)\n";
        $dbh->do("INSERT INTO location_points VALUES (NULL, $1, $2, $locIndex)") or die "Couldn't excute statement: " . $dbh->errstr;
    }elsif($line =~ /^<\!--/){
        exit;
    }
    
}



exit;
